/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package exportFile;

import java.io.File;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import screen.HeuristicsSelected;
import services.ServiceInformation;

/**
 *
 * @author Bruna
 */
public class ExportExcel {

    private WritableCellFormat timesBoldUnderline;
    private WritableCellFormat times;
    private WritableCellFormat times2;
    private WritableCellFormat times3;
    private WritableCellFormat times4;
    private WritableCellFormat times5;
    private String inputFile;
    private String path;
    private File file;
    

    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    public void write(List<List> services,HeuristicsSelected heurisSelec) throws IOException, WriteException {
        //File file = new File(inputFile);
        file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("pt", "PT"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        
        int numberSheet = 0;
        if(heurisSelec.isRegrasNegocioSelected()){
            workbook.createSheet("Regras de Negocio", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isMultiplaInstanciaSelected()){
            workbook.createSheet("Multipla Instancia", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isClusterSelected()){
            workbook.createSheet("Cluster", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isAtividadesSequenciaisSelected()){
            workbook.createSheet("Atividades Sequenciais", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isWorkflowsSelected()){
            workbook.createSheet("Workflow Loop", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isWorkflowsSelected()){
            workbook.createSheet("Workflow Xor", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isWorkflowsSelected()){
            workbook.createSheet("Workflow Or", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isWorkflowsSelected()){
            workbook.createSheet("Workflow And", numberSheet);
            numberSheet++;
        }
        
        /*workbook.createSheet("Regras de Negocio", 0);
        workbook.createSheet("Multipla Instancia", 1);
        workbook.createSheet("Cluster", 2);
        workbook.createSheet("Atividades Sequenciais", 3);
        workbook.createSheet("Workflow Loop", 4);
        workbook.createSheet("Workflow Xor", 5);
        workbook.createSheet("Workflow Or", 6);
        workbook.createSheet("Workflow And", 7);*/
        
        int i =1;
        for (List listService : services) {
            List<ServiceInformation> service = listService;
            String typeService = service.get(0).getOrigin().toString();
            
            if(typeService.equalsIgnoreCase("Regras de Negocio")){                
                WritableSheet excelSheet = workbook.getSheet("Regras de Negocio");
                createLabel(excelSheet);
                writeCaption(excelSheet);
                i = excelSheet.getRows();                
                createContent(excelSheet, listService, i++);
                
            }
            
            if(typeService.equalsIgnoreCase("Multipla Instancia")){
                WritableSheet excelSheet = workbook.getSheet("Multipla Instancia");
                createLabel(excelSheet);
                writeCaption(excelSheet);
                i = excelSheet.getRows();                
                createContent(excelSheet, listService, i++);
            }
            
            if(typeService.equalsIgnoreCase("Cluster")){
                WritableSheet excelSheet = workbook.getSheet("Cluster");
                createLabel(excelSheet);
                writeCaption(excelSheet);
                i = excelSheet.getRows();                
                createContent(excelSheet, listService, i++);
            }
            
            if(typeService.equalsIgnoreCase("Atividades Sequenciais")){
                WritableSheet excelSheet = workbook.getSheet("Atividades Sequenciais");
                createLabel(excelSheet);
                writeCaption(excelSheet);
                i = excelSheet.getRows();                
                createContent(excelSheet, listService, i++);
            }
            if(typeService.contains("Workflow")){                
                for (ServiceInformation serviceInformation : service) {
                    String tipo = serviceInformation.getOrigin();
                    
                    if (tipo.equalsIgnoreCase("Workflow Loop")) {
                        WritableSheet excelSheet = workbook.getSheet("Workflow Loop");
                        createLabel(excelSheet);
                        writeCaption(excelSheet);
                        i = excelSheet.getRows();
                        List<ServiceInformation> listServiceInf = new LinkedList<ServiceInformation>();
                        listServiceInf.add(serviceInformation);
                        createContent(excelSheet, listServiceInf, i++);
                    }

                    if (tipo.equalsIgnoreCase("Workflow XOR")) {
                        WritableSheet excelSheet = workbook.getSheet("Workflow Xor");
                        createLabel(excelSheet);
                        writeCaption(excelSheet);
                        i = excelSheet.getRows();
                        List<ServiceInformation> listServiceInf = new LinkedList<ServiceInformation>();
                        listServiceInf.add(serviceInformation);
                        createContent(excelSheet, listServiceInf, i++);
                    }

                    if (tipo.equalsIgnoreCase("Workflow OR")) {
                        WritableSheet excelSheet = workbook.getSheet("Workflow Or");
                        createLabel(excelSheet);
                        writeCaption(excelSheet);
                        i = excelSheet.getRows();
                        List<ServiceInformation> listServiceInf = new LinkedList<ServiceInformation>();
                        listServiceInf.add(serviceInformation);
                        createContent(excelSheet, listServiceInf, i++);
                    }

                    if (tipo.equalsIgnoreCase("Workflow AND")) {
                        WritableSheet excelSheet = workbook.getSheet("Workflow And");
                        createLabel(excelSheet);
                        writeCaption(excelSheet);
                        i = excelSheet.getRows();
                        List<ServiceInformation> listServiceInf = new LinkedList<ServiceInformation>();
                        listServiceInf.add(serviceInformation);
                        createContent(excelSheet, listServiceInf, i++);
                    }
                }
            }                     
        }
        
        workbook.write();
        workbook.close();
        path = file.getAbsolutePath();
    }
    
    public void writeConsolidated(List<List> servicesClusterConsolidated, List<List> allservices, List<String> allActivities, HeuristicsSelected heurisSelec) throws IOException, WriteException {
        file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("pt", "PT"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        //workbook.createSheet("Cluster Consolidado", 0);
        
        int numberSheet = 0;
        if(heurisSelec.isSubflowsSelected()){
            workbook.createSheet("Flows", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isServAtivSelected()){
            workbook.createSheet("Serviços x Atividades", numberSheet);
            numberSheet++;
        }
        if(heurisSelec.isServPerfisSelected()){
            workbook.createSheet("Serviços x Perfil", numberSheet);
            numberSheet++;
        }
        //workbook.createSheet("Flows", 0);
        //workbook.createSheet("Serviços x Atividades", 1);
        //workbook.createSheet("Serviços x Perfil", 2);
        
        int i =1;
        /*for (List listService : servicesClusterConsolidated) {
            
            WritableSheet excelSheet = workbook.getSheet(0);
            createLabel(excelSheet);
            i = excelSheet.getRows();                
            createContent(excelSheet, listService, i++);
        }*/        
                
        for(List serv : allservices){
            if(heurisSelec.isSubflowsSelected()){            
                WritableSheet excelSheet = workbook.getSheet("Flows");
                createLabel(excelSheet);
                addCaption(excelSheet, 0, 0, "Serviços");
                addCaption(excelSheet, 1, 0, "Nº de Subflows");
                addCaption(excelSheet, 2, 0, "Nº de Raias");
                addCaption(excelSheet, 3, 0, "Cluster Associados");
                i = excelSheet.getRows();                
                createContentSubflows(excelSheet, serv, i++); 
            }
        
        
            if(heurisSelec.isServAtivSelected()){            
                WritableSheet excelSheet2 = workbook.getSheet("Serviços x Atividades");
                createLabel(excelSheet2);
                addCaption(excelSheet2, 0, 0, "Serviços");
                int j=1;
                for(String name : allActivities){
                    addCaption(excelSheet2, j, 0, name);
                    j++;
                }            
                i = excelSheet2.getRows();
                createContentServXActiv(excelSheet2, serv, i++);
            }
        
        
            if(heurisSelec.isServPerfisSelected()){
                WritableSheet excelSheet3 = workbook.getSheet("Serviços x Perfil");
                createLabel(excelSheet3);
                addCaption(excelSheet3, 0, 0, "Serviços");
                addCaption(excelSheet3, 1, 0, "Atendente");
                addCaption(excelSheet3, 2, 0, "Analista de Credito");
                addCaption(excelSheet3, 3, 0, "Credito Direto");
                i = excelSheet3.getRows();
                createContentServXRoles(excelSheet3, serv, i++);
            }
        }       
        
        workbook.write();
        workbook.close();
        path = file.getAbsolutePath();
    }
    
    private void createContentServXActiv(WritableSheet sheet, List<ServiceInformation> services, int i) throws WriteException, RowsExceededException {     

        sheet.setColumnView(0, 30); //A
        sheet.setColumnView(1, 27); //B
        sheet.setColumnView(2, 34); //C
        sheet.setColumnView(3, 17); //D
        sheet.setColumnView(4, 25); //E         
        sheet.setColumnView(5, 43); //F
        sheet.setColumnView(6, 27); //G        
        sheet.setColumnView(7, 28); //H
        sheet.setColumnView(8, 48); //I
        sheet.setColumnView(9, 17); //J
        sheet.setColumnView(10, 44); //K
        sheet.setColumnView(11, 17); //L
        sheet.setColumnView(12, 26); //M
        sheet.setColumnView(13, 17); //N
        sheet.setColumnView(14, 26); //O
        sheet.setColumnView(15, 24); //P
        sheet.setColumnView(16, 33); //Q
        
        int numRows = i;
        for (ServiceInformation serviceInformation : services) {
            if(i%2==0){
                addLabel3(sheet, 0, i, serviceInformation.getName());
            }else{
                addLabel2(sheet, 0, i, serviceInformation.getName());
            }
            
            //addLabel(sheet, 0, i, serviceInformation.getName());
            List<String> listActivities = serviceInformation.getActivities();
            for(String activity: listActivities){

                int numColums= sheet.getColumns();
                for(int k =1;k<numColums;k++){
                    String nameColum = sheet.getColumn(k)[0].getContents().toString();
                    
                    if(activity.equalsIgnoreCase(nameColum)){
                        if(i%2==0){
                            addLabel5(sheet, k, i, "X");
                        }else{
                            addLabel4(sheet, k, i, "X");
                        }                        
                        //addLabel4(sheet, k, i, "X");
                    }
                    
                }             
                
            }
            i++;
            numRows = i;
        }
        
        int numColums= sheet.getColumns();
        for(int k =1;k<numRows;k++){
            for(int col = 1;col<numColums;col++){
               String contentCell = sheet.getCell(col, k).getContents().toString();
               
               if(contentCell.isEmpty()){                
                    if(k%2==0){
                        addLabel3(sheet, col, k, "");
                    }else{
                        addLabel2(sheet, col, k, "");
                    }
                }
            }
        }
    }
    
    private void createContentServXRoles(WritableSheet sheet, List<ServiceInformation> services, int i) throws WriteException, RowsExceededException {     

        sheet.setColumnView(0, 37);
        sheet.setColumnView(1, 11);
        sheet.setColumnView(2, 20);
        sheet.setColumnView(3, 15);
        
        int numRows = i;
        for (ServiceInformation serviceInformation : services) {
            if(i%2 ==0){
                //é par
                addLabel3(sheet, 0, i, serviceInformation.getName());
            }
            else{
                addLabel2(sheet, 0, i, serviceInformation.getName());
            }
            
            //addLabel(sheet, 0, i, serviceInformation.getName());
            List<String> listRole = serviceInformation.getProfile();
            for(String role: listRole){
                if(role.equalsIgnoreCase("Atendente")){
                    if(i%2==0){
                        addLabel5(sheet, 1, i, "X");
                    }else{
                        addLabel4(sheet, 1, i, "X");
                    }
                    //addLabel4(sheet, 1, i, "X");
                }
                if(role.equalsIgnoreCase("Analista de Credito")){
                    if(i%2==0){
                        addLabel5(sheet, 2, i, "X");
                    }else{
                        addLabel4(sheet, 2, i, "X");
                    }
                    //addLabel4(sheet, 2, i, "X");
                }
                if(role.equalsIgnoreCase("Credito Direto")){
                    if(i%2==0){
                        addLabel5(sheet, 3, i, "X");
                    }else{
                        addLabel4(sheet, 3, i, "X");
                    }
                    //addLabel4(sheet, 3, i, "X");
                }
            }
            i++;
            numRows++;
        }
        
        int numColums= sheet.getColumns();
        for(int k =1;k<numRows;k++){
            for(int col = 1;col<numColums;col++){
               String contentCell = sheet.getCell(col, k).getContents().toString();
               
               if(contentCell.isEmpty()){                
                    if(k%2==0){
                        addLabel3(sheet, col, k, "");
                    }else{
                        addLabel2(sheet, col, k, "");
                    }
                }
            }
        }                          

    }
    
    private void createContentSubflows(WritableSheet sheet, List<ServiceInformation> services, int i) throws WriteException, RowsExceededException {
        
        sheet.setColumnView(0, 37);
        sheet.setColumnView(1, 15);
        sheet.setColumnView(2, 12);
        sheet.setColumnView(3, 45);
        
        int numRows = i;
        
        for (ServiceInformation serviceInformation : services) {
            if( (serviceInformation.getOrigin().equalsIgnoreCase("Workflow Loop")) 
                    || (serviceInformation.getOrigin().equalsIgnoreCase("Workflow Xor")) 
                    || (serviceInformation.getOrigin().equalsIgnoreCase("Workflow Or"))
                    || (serviceInformation.getOrigin().equalsIgnoreCase("Workflow And")) ){            
                
                    if (i % 2 == 0) {
                        //é par
                        addLabel3(sheet, 0, i, serviceInformation.getName());
                        addNumber(sheet, 1, i, serviceInformation.getSubflows());                        
                        addNumber(sheet, 2, i, serviceInformation.getProfile().size());
                        List<String> clustersAssociated = new LinkedList<String>();
                        clustersAssociated = serviceInformation.getEntry();
                        for (String exit : serviceInformation.getExit()) {
                            if(!clustersAssociated.contains(exit)){
                                clustersAssociated.add(exit);                                
                            }                            
                        }
                        addLabel3(sheet, 3, i, clustersAssociated.toString());
                    } else {
                        addLabel2(sheet, 0, i, serviceInformation.getName());
                        addNumber2(sheet, 1, i, serviceInformation.getSubflows());
                        addNumber2(sheet, 2, i, serviceInformation.getProfile().size());
                        List<String> clustersAssociated = new LinkedList<String>();
                        clustersAssociated = serviceInformation.getEntry();
                        for (String exit : serviceInformation.getExit()) {
                            if(!clustersAssociated.contains(exit)){
                                clustersAssociated.add(exit);                                
                            }                            
                        }
                        addLabel2(sheet, 3, i, clustersAssociated.toString());
                    }
                    i++;
                    numRows++;
            }                   
        }
    }
    
    private void createLabel(WritableSheet sheet) throws WriteException {
        // Lets create a times font
        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
        // Define the cell format
        times = new WritableCellFormat(times10pt);
        // Lets automatically wrap the cells
        times.setWrap(false);
        
        
        times2 = new WritableCellFormat(times10pt);
        times2.setWrap(false);
        times2.setBackground(Colour.GREY_40_PERCENT);
        times2.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.WHITE);
        
        times3 = new WritableCellFormat(times10pt);
        times3.setWrap(false);
        times3.setBackground(Colour.GREY_25_PERCENT);
        times3.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.WHITE);
        
        times4 = new WritableCellFormat(times10pt);
        times4.setWrap(false);
        times4.setBackground(Colour.GREY_40_PERCENT);
        times4.setAlignment(Alignment.CENTRE);
        times4.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.WHITE);
        
        times5 = new WritableCellFormat(times10pt);
        times5.setWrap(false);
        times5.setBackground(Colour.GREY_25_PERCENT);
        times5.setAlignment(Alignment.CENTRE);
        times5.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.WHITE);

        // Create create a bold font with unterlines
        WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false, UnderlineStyle.SINGLE, Colour.WHITE);
                //(WritableFont.TIMES, 12, WritableFont.BOLD, false, UnderlineStyle.SINGLE, Colour.WHITE);
                //(WritableFont.TIMES, 12, WritableFont.BOLD, false, UnderlineStyle.SINGLE);
        timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
        // Lets automatically wrap the cells
        timesBoldUnderline.setWrap(false);
        
        timesBoldUnderline.setBackground(Colour.DEFAULT_BACKGROUND);
        timesBoldUnderline.setAlignment(Alignment.LEFT);
        timesBoldUnderline.setVerticalAlignment(VerticalAlignment.CENTRE);

        CellView cv = new CellView();
        cv.setFormat(times);
        cv.setFormat(timesBoldUnderline);
        cv.setAutosize(true);

        // Write a few headers
        //writeCaption(sheet);
        //addCaption(sheet, 0, 0, "Informações");
        //addCaption(sheet, 1, 0, "Serviços");


    }

    private void createContent(WritableSheet sheet, List<ServiceInformation> services, int i) throws WriteException, RowsExceededException {     
        
        sheet.setColumnView(0, 13);
        sheet.setColumnView(1, 37);

        for (ServiceInformation serviceInformation : services) {
            
            addLabel2(sheet, 0, i, "Nome");
            addLabel2(sheet, 1, i, serviceInformation.getName());
            i++;
            addLabel3(sheet, 0, i, "Tipo");
            addLabel3(sheet, 1, i, serviceInformation.getType());
            i++;
            addLabel2(sheet, 0, i, "Entrada");
            addLabel2(sheet, 1, i, serviceInformation.getEntry().toString());
            i++;
            addLabel3(sheet, 0, i, "Saída");
            addLabel3(sheet, 1, i, serviceInformation.getExit().toString());
            i++;
            addLabel2(sheet, 0, i, "Origem");
            addLabel2(sheet, 1, i, serviceInformation.getOrigin());
            i++;
            addLabel3(sheet, 0, i, "Atividades");
            addLabel3(sheet, 1, i, serviceInformation.getActivities().toString());
            i++;
            addLabel2(sheet, 0, i, "Descrição");
            addLabel2(sheet, 1, i, serviceInformation.getDescription());
            i++;
            addLabel3(sheet, 0, i, "Perfil");
            addLabel3(sheet, 1, i, serviceInformation.getProfile().toString());
            i++;
            addLabel(sheet, 0, i, "");
            addLabel(sheet, 1, i, "");
            i++;
        }
    }

    private void addCaption(WritableSheet sheet, int column, int row, String s) throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldUnderline);
        sheet.addCell(label);
    }

    private void addNumber(WritableSheet sheet, int column, int row, Integer integer) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, integer, times3);
        sheet.addCell(number);
    }
    
    private void addNumber2(WritableSheet sheet, int column, int row, Integer integer) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, integer, times2);
        sheet.addCell(number);
    }

    private void addLabel(WritableSheet sheet, int column, int row, String s) throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times);        
        sheet.addCell(label);
    }
    
    private void addLabel2(WritableSheet sheet, int column, int row, String s) throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times2);        
        sheet.addCell(label);
    }
    
    private void addLabel3(WritableSheet sheet, int column, int row, String s) throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times3);        
        sheet.addCell(label);
    }
    
    private void addLabel4(WritableSheet sheet, int column, int row, String s) throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times4);        
        sheet.addCell(label);
    }
    private void addLabel5(WritableSheet sheet, int column, int row, String s) throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times5);        
        sheet.addCell(label);
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }
    
    public void writeCaption(WritableSheet excelSheet){
        try {
            addCaption(excelSheet, 0, 0, "Informações");
            addCaption(excelSheet, 1, 0, "Serviços");
        } catch (RowsExceededException ex) {
            Logger.getLogger(ExportExcel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (WriteException ex) {
            Logger.getLogger(ExportExcel.class.getName()).log(Level.SEVERE, null, ex);
        }        
    }
}
